# This file is used to create the user_info_ext table in the database.

'''

CREATE TABLE IF NOT EXISTS user_info (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(15) NOT NULL,
    device_sn VARCHAR(50) NOT NULL,
    notification_type VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    current_status VARCHAR(50) DEFAULT 'active' -- active, blocked
);

'''
import logging


logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(name)s - %(levelname)s - %(message)s')

logger = logging.getLogger(__name__)

class UserInfoTableMethods: 

    def __init__(self, conn, cur):
        self.cursor = cur
        self.conn = conn

    def insert_user_info(self, name, phone, device_sn, notification_type):

        sql = "INSERT INTO user_info (name, phone, device_sn, notification_type) VALUES (?,?,?,?)"
        values = (name, phone, device_sn, notification_type)

        self.cursor.execute(sql, values)
        self.conn.commit()
        logger.info("User Info Inserted")

    def select_user_info_by_phone_and_device_sn(self, phone, device_sn):

        sql = "SELECT * FROM user_info WHERE phone =? AND device_sn =? and current_status = 'active'"
        values = (phone, device_sn)

        self.cursor.execute(sql, values)
        result = self.cursor.fetchone()

        return result
    
    def block_user_info(self, id):

        sql = "UPDATE user_info SET current_status = 'blocked' WHERE id =?"
        values = (id,)

        self.cursor.execute(sql, values)
        self.conn.commit()
        logger.info("User Info Blocked")

    

    def unblock_user_info(self, user_id):

        sql = "UPDATE user_info SET current_status = 'active' WHERE id =?"
        values = (id,)

        self.cursor.execute(sql, values)
        self.conn.commit()
        logger.info("User Info Unblocked")

    def update_user_info_notification_type_by_id(self, user_id, notification_type):


        sql = "UPDATE user_info SET notification_type =? WHERE id =?"
        values = (notification_type, user_id)

        self.cursor.execute(sql, values)
        self.conn.commit()
        logger.info("User Info Notification Type Updated")

    
    # 查询设备号对应的用户信息
    def select_user_info_by_device_sn(self, device_sn):

        sql = "SELECT * FROM user_info WHERE device_sn =? and current_status = 'active'"
        values = (device_sn,)

        self.cursor.execute(sql, values)

        result = self.cursor.fetchall()

        return result
    
    def select_device_sn_list(self):

        sql = "SELECT DISTINCT device_sn FROM user_info;"

        self.cursor.execute(sql)

        result = self.cursor.fetchall()

        return [i["device_sn"] for i in result]


             
